package db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import db.Controller;
import db.dto.DictionariesDTO;

public class DictionariesDAO {
//	Logger LOG = Logger.getLogger("DictionariesDAO");
	public List<DictionariesDTO> getDictionaries() {
		List<DictionariesDTO> listDict = new ArrayList<DictionariesDTO>();
		String query = "select * from m_dictionaries";
		Connection con = Controller.getConnection();
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while(rs.next()) {
				listDict.add(new DictionariesDTO(rs.getInt("m_id"), 
											rs.getInt("m_fromID"), 
											rs.getInt("m_toID"), 
											rs.getString("m_dicturl"), 
											rs.getInt("m_delCount")));
			}
		} catch (SQLException e) {
//			LOG.error(e);
			return null;
		}
		return listDict;
	}
	public DictionariesDTO getDictionary(int id) {
		DictionariesDTO dict = null;
		String query = "select * from m_dictionaries where m_id = ?";
		Connection con = Controller.getConnection();
		try {
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				dict = new DictionariesDTO();
				dict.setId(rs.getInt("m_id"));
				dict.setFrom(rs.getInt("m_fromID"));
				dict.setTo(rs.getInt("m_toID"));
				dict.setUrl(rs.getString("m_dicturl"));
				dict.setDelCount(rs.getInt("m_delCount"));
			}
		} catch (SQLException e) {
//			LOG.error(e);
			return null;
		}
		return dict;
	}
	public DictionariesDTO getDictionary(int from, int to) {
		DictionariesDTO dict = null;
		String query = "select * from m_dictionaries where m_fromID = ? and m_toID = ?";
		Connection con = Controller.getConnection();
		try {
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setInt(1, from);
			stmt.setInt(2, to);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				dict = new DictionariesDTO();
				dict.setId(rs.getInt("m_id"));
				dict.setFrom(rs.getInt("m_fromID"));
				dict.setTo(rs.getInt("m_toID"));
				dict.setUrl(rs.getString("m_dicturl"));
				dict.setDelCount(rs.getInt("m_delCount"));
			}
		} catch (SQLException e) {
//			LOG.error(e);
			return null;
		}
		return dict;
	}
	public boolean deleteDictionary(int id) {
		String query = "delete from m_dictionaries where m_id = ?";
		Connection con = Controller.getConnection();
		try {
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			stmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e);
//			LOG.error(e);
			return false;
		}
		return true;
	}
	public boolean deleteDictionary(int from, int to) {
		String query = "delete from m_dictionaries where m_fromID = ? and m_toID = ?";
		Connection con = Controller.getConnection();
		try {
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setInt(1, from);
			stmt.setInt(2, to);
			stmt.executeUpdate();
		} catch (SQLException e) {
//			LOG.error(e);
			return false;
		}
		return true;
	}
	public boolean addDictionary(int from, int to, String url) {
		String query = "insert into m_dictionaries(m_fromID, m_toID, m_dicturl, m_delCount) values(?, ?, ?, ?)";
		Connection con = Controller.getConnection();
		try {
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setInt(1, from);
			stmt.setInt(2, to);
			stmt.setString(3, url);
			stmt.setInt(4, 0);
			stmt.executeUpdate();
		} catch (SQLException e) {
//			LOG.error(e);
			return false;
		}
		return true;
	}
}
